﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LeHouse.Server.Dto;

namespace LeHouse.Server.Dal
{
    using ServiceStack.OrmLite;
    using System.Linq.Expressions;
    using System.Data;
    /// <summary>
    /// 创建用户信息表（抵押物权益状况）  登记记录 的DAL
    /// </summary>
    public class Collateral_PersonalDAL : GenericBaseDAL<Collateral_Personal>
    {
        /// <summary>
        /// 资产登记的数据添加And编辑
        /// </summary>
        /// <param name="parms"></param>
        /// <returns></returns>
        public bool PersonalOrBorrowerInsertOrUpdate(PersonalOrBorrower parms)
        {
            using (var cnn = DbConnection)
            {
                //开启事物
                var cbt = cnn.OpenTransaction();
                try
                {
                    if (parms.State == 1)
                    {
                        cnn.Insert(parms.Collateral_Personal);

                        foreach (Collateral_Borrower cb in parms.Collateral_BorrowerList)
                        {
                            cnn.Insert(cb);
                        }
                    }
                    if (parms.State == 1)
                    {
                        cnn.Update(parms.Collateral_Personal,
                            t => t.T_ReportNumber == parms.Collateral_Personal.T_ReportNumber);

                        cnn.Delete<Collateral_Borrower>(t => t.T_ReportNumber == parms.Collateral_Personal.T_ReportNumber);
                        
                        foreach (Collateral_Borrower cb in parms.Collateral_BorrowerList)
                        {
                            cnn.Insert(cb);
                        }
                    }
                    //提交
                    cbt.Commit();
                    return true;
                }
                catch
                {
                    //回滚
                    cbt.Rollback();
                    return false;
                }
            }
        }




        /// <summary>
        /// 项目号递增
        /// </summary>
        /// <returns></returns>
        public string SortReportNumber()
        {
            using (var cnn = DbConnection)
            {
                var d1 = cnn.From<Collateral_Personal>().OrderByDescending(p => p.T_ReportNumber);
                var ss = cnn.Select(d1).ToList().FirstOrDefault();
                if (ss == null)
                {
                    return DateTime.Now.Year + "00001";
                }
                else
                {
                    string sorttp = ss.T_ReportNumber.Replace(DateTime.Now.Year.ToString(), "");
                    return DateTime.Now.Year + (Convert.ToInt32(sorttp) + 1).ToString();
                }
            }
        }
        /// <summary>
        /// 根据报告号去查基本信息
        /// </summary>
        /// <param name="P_ReportNumber"></param>
        /// <returns></returns>
        public Jointshow selectbyid(string P_ReportNumber)
        {
            Jointshow per = new Jointshow();
            string sql = @"
select a.T_PersonalID 主键 ,a.T_ReportNumber 报告号 ,a.T_Linkman 看房联系人 ,a.T_Phone 看房联系人电话 ,a.T_Loanyear 房屋剩余年限 ,
a.T_Loanlimit 贷款额度 ,a.T_Actual 实际交易价 ,a.T_Located 房屋坐落 ,a.T_Activate 建成年代 
 ,a.T_Assess 评估受理    ,a.T_Date 日期 ,a.T_State 状态  ,b.现场勘查所属员工,b.房屋用途,b.部门,c.DictionaryName,d.T_Name  from  Collateral_Personal a  
  left join  
(select  b.T_ReportNumber, c.T_Name 现场勘查所属员工,d.T_Dname 部门,e.DictionaryName 房屋用途 from 
 Collateral_Personal b join System_Staff c on b.T_SiteSurveyid=c.T_StaffID
 left join System_Department  d on b.T_DepartmentID=d.T_DepartmentID
  left join System_Dictionary e on b.T_PurposeID=e.DictionaryID  where   b.T_ReportNumber=@P_ReportNumber )   b on a.T_ReportNumber=b.T_ReportNumber

join(
  select  DictionaryID,DictionaryName from System_Dictionary  where  DictionaryID=(  select  T_ReportID from Collateral_Personal where T_ReportNumber= @P_ReportNumber)
) c
on a.T_ReportID=c.DictionaryID
left join (
  select T_StaffID,T_Name from System_Staff where  T_StaffID=(select T_StaffID from Collateral_Personal where T_ReportNumber= @P_ReportNumber)
)d  on a.T_SiteSurveyid=d. T_StaffID
where a. T_ReportNumber=@P_ReportNumber";
            using (var cnn = DbConnection)
            {
                //var CP = cnn.CreateParam("@P_ReportNumber");
                //CP.Value = P_ReportNumber;
                //List<IDbDataParameter> ls = new List<IDbDataParameter>();
                //ls.Add(CP);
                //ls.Add(CP);
                //ls.Add(CP);
                //ls.Add(CP);
                //var list = cnn.SqlList<DataTable>(sql, ls);
                using (var cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    var p = cmd.CreateParameter();
                    p.ParameterName = "@P_ReportNumber";
                    p.Value = P_ReportNumber;
                    cmd.Parameters.Add(p);
                    var reader = cmd.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            per.T_ReportNumber = reader[1].ToString();
                            per.T_Linkman = reader[2].ToString();
                            per.T_Phone = reader[3].ToString();
                            per.T_Loanyear = reader[4].ToString();
                            per.T_Loanlimit = reader[5].ToString();
                            per.T_Actual = reader[6].ToString();
                            per.T_Located = reader[7].ToString();
                            per.T_Activate = reader[8].ToString();
                            per.T_Assess = reader[9].ToString();
                            per.T_Date = Convert.ToDateTime(reader[10]);
                            switch (Convert.ToInt32(reader[11]))
                            {
                                case 0: per.T_State = "待约勘"; break;
                                case 1: per.T_State = "待查勘"; break;
                                case 2: per.T_State = "已查勘"; break;
                                case 3: per.T_State = "待审核"; break;
                                case 4: per.T_State = "初审未通过"; break;
                                case 5: per.T_State = "初审通过"; break;
                                case 6: per.T_State = "终审未通过"; break;
                                case 7: per.T_State = "终审通过"; break;
                                default: break;
                            }
                            per.T_State = Convert.ToInt32(reader[11]) == 0 ? "待约勘" : "暂无";
                            per.T_PurposeID = reader[13].ToString();
                            per.T_ReportID = reader[15].ToString();
                            per.T_DepartmentID = reader[14].ToString();
                            per.T_SiteSurveyid = reader[12].ToString();
                            per.T_Iid = reader[16].ToString();
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                return per;
            }
        }
        /// <summary>
        /// 根据报告号去查约没约到加载显示
        /// </summary>
        /// <param name="P_ReportNumber"></param>
        /// <returns></returns>
        public Collateral_Personal selectbyP_ReportNumberss(string P_ReportNumber)
        {
            Collateral_Personal per = new Collateral_Personal();
            string sql = @"  select P_state,P_cause from t_Personal where P_ReportNumber = @P_ReportNumber";
            using (var cnn = DbConnection)
            {
                using (var cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    var p = cmd.CreateParameter();
                    p.ParameterName = "@P_ReportNumber";
                    p.Value = P_ReportNumber;
                    cmd.Parameters.Add(p);
                    var reader = cmd.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            //   per.P_state = Convert.ToInt32(reader["P_state"]);
                            //   per.P_cause = reader["P_cause"].ToString();

                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                return per;
            }
        }




        /// <summary>
        /// WAP 端请求模糊查询地址
        /// </summary>
        /// <param name="P_located"></param>
        /// <returns></returns>
        public List<Collateral_Personal> searchbyP_located(string C_project)
        {
            List<Collateral_Personal> P_locatedlist = new List<Collateral_Personal>();
            string sql = @"select a.P_located,b.I_Buildingafew,a.P_ReportNumber,c.C_project from t_Personal  a  left join t_Indoor b on a.P_ReportNumber=b.P_ReportNumber  left join t_Community c on a.P_ReportNumber=c.P_ReportNumber where P_located like @C_project";
            using (var cnn = DbConnection)
            {
                using (var cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    var p = cmd.CreateParameter();
                    p.ParameterName = "@C_project";
                    p.Value = "%" + C_project + "%";
                    cmd.Parameters.Add(p);
                    var reader = cmd.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            System_Department per = new System_Department();
                            //   per = reader["C_project"].ToString();
                            //  per.P_located = reader["P_located"].ToString();
                            //  per. = Convert.ToInt32(reader["I_Buildingafew"]);
                            //   per.= reader["P_ReportNumber"].ToString();
                            //   P_locatedlist.Add(per);
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                return P_locatedlist;
            }
        }
    }
}

